Stored Procedure |
| A stored procedure is small program that runs in the database. It can be called from any programming language. As it is in the database, it can be updated easily. The example shown below illustrates how to create a stored procedure to add two numbers. In the same way, the program illustrates how to call this stored procedure. See Publishing a Web Site > Public SQL web site to learn how to setup an user account in the database so that an user can execute store procedures. |
| Problem 1 |
| Create a program called SpTest to call a stored procedure from a database. Observe that each input or output parameter in the stored procedure is represented by a question mark. In this case, there are three parameters (in_x, in_y, out_z), therefore there are three question marks. |
| best_buy.sql |
| ... CREATE PROCEDURE proc_suma @in_x DECIMAL(8, 3), @in_y DECIMAL(8, 3), @out_z DECIMAL(8, 3) OUTPUT AS SET @out_z = @in_x + @in_y; RETURN 0; |
| SpTest.cpp |
| void SpTest:: btOK_Click(Win::Event& e) { Sql::SqlConnection conn; try { //conn.OpenSession(DSN, USERNAME, PASSWORD); //Control Panel>Administrative Tools>Data Sources (ODBC)>Create dsn_myDatabase conn.OpenSession(hWnd, CONNECTION_STRING); double x = 10.8; double y =15.3; double z; conn.CreateStatement(); conn.BindInputParameter(1, x); conn.BindInputParameter(2, y); conn.BindOutputParameter(3, z); int returnValue = conn.ExecDirect(L"{call proc_suma(?, ?, ?)}"); //A question mark for each param this->MessageBox(Sys::Convert::ToString(z), L"The summation is ", MB_OK); //returnValue must be 1 conn.DestroyStatement(); } catch (Sql::SqlException e) { this->MessageBox(e.GetDescription(), L"Error", MB_OK | MB_ICONERROR); } } |